﻿// ===============================================================================
// XCI.NET 西安交通信息WinForm开发框架 2018 版权所有
// ===============================================================================
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading;
using XCI.Core;
using XCI.Data.Entity;
using XCI.Data.Expressions;
using XCI.Data.Expressions.Compiler;
using XCI.Helper;
using XCI.Logger;

namespace XCI.Data
{
    /// <summary>
    /// 数据库对象
    /// </summary>
    public class Database : IDisposable
    {
        #region 私有变量

        private readonly DatabaseSetting _setting;
        private readonly DbProviderFactory _dbProviderFactory;
        private readonly string _paramPrefix;
        private readonly IDbProvider _provider;
        private DbConnection _sharedConnection;
        private DbTransaction _transaction;
        private int _sharedConnectionDepth;
        private int _transactionDepth;
        private bool _transactionCancelled;
        private readonly Stopwatch _watch = new Stopwatch();
        private readonly ILogger logger = DatabaseFactory.Logger;

        #endregion

        #region 构造函数

        /// <summary>
        /// 初始化数据库对象
        /// </summary>
        public Database() : this(DatabaseFactory.GetSetting())
        {
        }

        /// <summary>
        /// 初始化数据库对象,读取指定名称的数据库配置
        /// </summary>
        /// <param name="name">配置名称</param>
        public Database(string name) : this(DatabaseFactory.GetSetting(name))
        {
        }


        /// <summary>
        /// 初始化数据库对象,使用指定的数据库配置
        /// </summary>
        /// <param name="setting">数据库配置</param>
        public Database(DatabaseSetting setting)
        {
            _setting = setting;
            _provider = DatabaseFactory.GetProvider(setting.Provider);
            _transactionDepth = 0;
            _paramPrefix = _provider.GetParamPrefix();
            _dbProviderFactory = _provider.GetDbProviderFactory();
        }

        #endregion

        #region 公共属性

        /// <summary>
        /// 设置查询超时时间
        /// </summary>
        public int CommandTimeout { get; set; }

        /// <summary>
        /// 设置查询超时时间(仅对下面执行的一句语句有效)
        /// </summary>
        public int OneTimeCommandTimeout { get; set; }

        /// <summary>
        /// 数据库引擎对象
        /// </summary>
        public IDbProvider Provider => _provider;

        #endregion

        #region 连接管理

        /// <summary>
        /// 保持连接打开状态
        /// </summary>
        public bool KeepConnectionAlive { get; set; }

        /// <summary>
        /// 打开连接
        /// </summary>
        public void OpenSharedConnection()
        {
            if (_sharedConnectionDepth == 0)
            {
                _sharedConnection = CreateConnection();
                _sharedConnection.ConnectionString = _setting.ConnectionString;

                if (_sharedConnection.State == ConnectionState.Broken)
                    _sharedConnection.Close();

                OnConnectionOpened(_sharedConnection);

                if (KeepConnectionAlive)
                    _sharedConnectionDepth++;
            }
            if (_sharedConnection.State == ConnectionState.Closed)
            {
                _sharedConnection.Open();
            }
            _sharedConnectionDepth++;
        }

        /// <summary>
        /// 关闭连接
        /// </summary>
        public void CloseSharedConnection()
        {
            if (_sharedConnectionDepth > 0)
            {
                _sharedConnectionDepth--;
                if (_sharedConnectionDepth == 0)
                {
                    OnConnectionClosing(_sharedConnection);
                    _sharedConnection.Close();
                    _sharedConnection = null;
                }
            }
        }

        #endregion

        #region 事务管理

        /// <summary>
        /// 开始事务
        /// </summary>
        public void BeginTran()
        {
            _transactionDepth++;

            if (_transactionDepth == 1)
            {
                OpenSharedConnection();
                _transaction = _sharedConnection.BeginTransaction();
                _transactionCancelled = false;
                OnBeginTransaction();
            }
        }

        /// <summary>
        /// 清理事务
        /// </summary>
        private void CleanupTransaction()
        {
            if (_transactionCancelled)
            {
                _transaction.Rollback();
            }
            else
            {
                _transaction.Commit();
            }

            OnEndTransaction();

            _transaction.Dispose();
            _transaction = null;

            CloseSharedConnection();
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void Rollback()
        {
            _transactionCancelled = true;
            if ((--_transactionDepth) == 0)
                CleanupTransaction();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void Commit()
        {
            if ((--_transactionDepth) == 0)
                CleanupTransaction();
        }

        #endregion

        #region 数据库对象

        /// <summary>
        /// 创建命令新连接
        /// </summary>
        /// <returns>返回连接新实例</returns>
        public DbConnection CreateConnection()
        {
            return _dbProviderFactory.CreateConnection();
        }

        /// <summary>
        /// 创建命令新实例
        /// </summary>
        /// <returns>返回命令新实例</returns>
        public DbCommand CreateCommand()
        {
            return _dbProviderFactory.CreateCommand();
        }

        /// <summary>
        /// 创建参数新实例
        /// </summary>
        /// <returns>返回参数新实例</returns>
        public DbParameter CreateParameter()
        {
            return _dbProviderFactory.CreateParameter();
        }

        /// <summary>
        /// 创建适配器新实例
        /// </summary>
        /// <returns>返回适配器新实例</returns>
        public DbDataAdapter CreateDataAdapter()
        {
            return _dbProviderFactory.CreateDataAdapter();
        }

        #endregion

        #region 命令对象管理

        /// <summary>
        /// 设置参数属性
        /// </summary>
        /// <param name="p">参数对象</param>
        /// <param name="value">参数值</param>
        private void SetParam(DbParameter p, object value)
        {
            if (value == null)
            {
                p.Value = DBNull.Value;
                //p.DbType = CSharpTypeToDbType();
            }
            else
            {
                value = ConvertParamValue(value);

                var t = value.GetType();
                if (t.IsEnum)
                {
                    p.Value = (int)value;
                }
                else if (t == typeof(Guid))
                {
                    p.Value = value.ToString();
                    p.DbType = DbType.String;
                    p.Size = 40;
                }
                else if (t == typeof(string))
                {
                    var v = value as string;
                    if (v != null)
                    {
                        v = v.Trim();
                    }
                    if (!string.IsNullOrEmpty(v))
                    {
                        p.Size = Math.Max(v.Length + 1, 4000);
                    }
                    p.Value = v;
                }
                else
                {
                    p.Value = value;
                }
            }
        }

        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="cmd">命令对象</param>
        /// <param name="name">参数名称</param>
        /// <param name="value">参数值</param>
        private void AddParam(DbCommand cmd, string name, object value)
        {
            name = ParamHelper.ParamsPrefixRegex.Replace(name, m => m.Value.Substring(1));
            if (value is IDbDataParameter idbParam)
            {
                idbParam.ParameterName = $"{_paramPrefix}{name}";
                cmd.Parameters.Add(idbParam);
                return;
            }

            var p = cmd.CreateParameter();
            //p.ParameterName = string.Format("{0}{1}", _paramPrefix, name);
            p.ParameterName = name;
            if (value is OutParam outParam)
            {
                p.Direction = ParameterDirection.Output;
                p.Size = 8000;
                outParam.InnerParam = p;
                if (outParam.IsCursor)
                {
                    _provider.SetCursorParam(p);
                }
            }
            else
            {
                SetParam(p, value);
            }
            cmd.Parameters.Add(p);
        }

        /// <summary>
        /// 创建SQL语句命令对象
        /// </summary>
        /// <param name="connection">连接对象</param>
        /// <param name="sql">查询语句</param>
        /// <param name="args">参数</param>
        /// <returns>命令对象</returns>
        public DbCommand CreateCommand(DbConnection connection, string sql, object args)
        {
            if (sql == null)
            {
                throw new ArgumentNullException(nameof(sql), "sql语句不能为null");
            }
            DbCommand cmd = CreateCommand();
            if (args != null)
            {
                // 参数解析
                var argsDest = new List<KeyValuePair<string, object>>();
                sql = ParamHelper.ParserParameter(sql, args, argsDest);
                foreach (var item in argsDest)
                {
                    AddParam(cmd, item.Key, item.Value);
                }
            }
            if (_paramPrefix != "@")
            {
                sql = ParamHelper.ParamsPrefixRegex.Replace(sql, m => _paramPrefix + m.Value.Substring(1));
            }
            sql = sql.Replace("@@", "@");

            if (connection != null)
            {
                cmd.Connection = connection;
                cmd.Transaction = _transaction;
            }
            cmd.CommandText = sql;
            return cmd;
        }

        /// <summary>
        /// 创建SQL语句命令对象
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="args">参数</param>
        /// <returns>命令对象</returns>
        public DbCommand CreateCommand(string sql, object args)
        {
            return CreateCommand(null, sql, args);
        }

        /// <summary>
        /// 创建存储过程命令对象
        /// </summary>
        /// <param name="connection">连接对象</param>
        /// <param name="storeProc">存储过程定义</param>
        /// <returns>命令对象</returns>
        public DbCommand CreateCommand(DbConnection connection, StoreProc storeProc)
        {
            DbCommand cmd = CreateCommand();
            if (storeProc.Args != null)
            {
                // 参数解析
                var argsDest = new List<KeyValuePair<string, object>>();
                ParamHelper.ParserStoreProcParameter(storeProc.Args, argsDest);
                foreach (var item in argsDest)
                {
                    AddParam(cmd, item.Key, item.Value);
                }
            }

            if (connection != null)
            {
                cmd.Connection = connection;
                cmd.Transaction = _transaction;
            }
            cmd.CommandText = storeProc.Name;
            cmd.CommandType = CommandType.StoredProcedure;
            return cmd;
        }

        /// <summary>
        /// 创建存储过程命令对象
        /// </summary>
        /// <param name="storeProc">存储过程定义</param>
        /// <returns>命令对象</returns>
        public DbCommand CreateCommand(StoreProc storeProc)
        {
            return CreateCommand(null, storeProc);
        }

        #endregion

        #region Execute

        /// <summary>
        /// 执行命令并返回影响的行数(参数值数组)
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="arrayArgs">参数值数组</param>
        /// <returns>返回受影响的行数</returns>
        public int Execute(string sql, object[] arrayArgs)
        {
            return Execute(sql, args: arrayArgs);
        }

        /// <summary>
        /// 执行命令并返回影响的行数(参数字典)
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="dicArgs">参数字典</param>
        /// <returns>返回受影响的行数</returns>
        public int Execute(string sql, IDictionary<string, object> dicArgs)
        {
            return Execute(sql, args: dicArgs);
        }

        /// <summary>
        /// 执行命令并返回影响的行数(参数对象)
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="args">参数对象</param>
        /// <returns>返回受影响的行数</returns>
        public int Execute(string sql, object args = null)
        {
            try
            {
                OpenSharedConnection();
                try
                {
                    using (var cmd = CreateCommand(_sharedConnection, sql, args))
                    {
                        _provider.BeforeExecute(cmd);
                        DoPreExecute(cmd);
                        OnExecutingCommand(cmd);
                        var retv = cmd.ExecuteNonQuery();
                        OnExecutedCommand(cmd);
                        return retv;
                    }
                }
                finally
                {
                    CloseSharedConnection();
                }
            }
            catch (Exception x)
            {
                if (OnException(x))
                    throw;
                return -1;
            }
        }

        /// <summary>
        /// 执行命令并返回影响的行数
        /// </summary>
        /// <param name="storeProc">存储过程</param>
        /// <returns>返回受影响的行数</returns>
        public int Execute(StoreProc storeProc)
        {
            try
            {
                OpenSharedConnection();
                try
                {
                    using (var cmd = CreateCommand(_sharedConnection, storeProc))
                    {
                        _provider.BeforeExecute(cmd);
                        DoPreExecute(cmd);
                        OnExecutingCommand(cmd);
                        var retv = cmd.ExecuteNonQuery();
                        OnExecutedCommand(cmd);
                        return retv;
                    }
                }
                finally
                {
                    CloseSharedConnection();
                }
            }
            catch (Exception x)
            {
                if (OnException(x))
                    throw;
                return -1;
            }
        }

        /// <summary>
        /// 执行命令并返回影响的行数
        /// </summary>
        /// <param name="cmd">命令对象</param>
        /// <returns>返回受影响的行数</returns>
        public int Execute(DbCommand cmd)
        {
            try
            {
                OpenSharedConnection();
                try
                {
                    cmd.Connection = _sharedConnection;
                    cmd.Transaction = _transaction;
                    _provider.BeforeExecute(cmd);
                    DoPreExecute(cmd);
                    OnExecutingCommand(cmd);
                    var retv = cmd.ExecuteNonQuery();
                    OnExecutedCommand(cmd);
                    return retv;
                }
                finally
                {
                    CloseSharedConnection();
                }
            }
            catch (Exception x)
            {
                if (OnException(x))
                    throw;
                return -1;
            }
        }

        #endregion

        #region ExecuteScalar

        /// <summary>
        /// 执行命令并返回第一行第一列数据(参数值数组)
        /// </summary>
        /// <typeparam name="T">目标数据类型</typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="arrayArgs">参数值数组</param>
        /// <returns>返回第一行第一列数据</returns>
        public T ExecuteScalar<T>(string sql, object[] arrayArgs)
        {
            return ExecuteScalar<T>(sql, args: arrayArgs);
        }

        /// <summary>
        /// 执行命令并返回第一行第一列数据(参数字典)
        /// </summary>
        /// <typeparam name="T">目标数据类型</typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="dicArgs">参数字典</param>
        /// <returns>返回第一行第一列数据</returns>
        public T ExecuteScalar<T>(string sql, IDictionary<string, object> dicArgs)
        {
            return ExecuteScalar<T>(sql, args: dicArgs);
        }

        /// <summary>
        /// 执行命令并返回第一行第一列数据(参数对象)
        /// </summary>
        /// <typeparam name="T">目标数据类型</typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="args">参数对象</param>
        /// <returns>返回第一行第一列数据</returns>
        public T ExecuteScalar<T>(string sql, object args = null)
        {
            try
            {
                OpenSharedConnection();
                try
                {
                    using (var cmd = CreateCommand(_sharedConnection, sql, args))
                    {
                        _provider.BeforeExecute(cmd);
                        DoPreExecute(cmd);
                        OnExecutingCommand(cmd);
                        object val = cmd.ExecuteScalar();
                        OnExecutedCommand(cmd);

                        Type u = Nullable.GetUnderlyingType(typeof(T));
                        if (u != null && (val == null || val == DBNull.Value))
                            return default(T);

                        return (T)Convert.ChangeType(val, u ?? typeof(T));
                    }
                }
                finally
                {
                    CloseSharedConnection();
                }
            }
            catch (Exception x)
            {
                if (OnException(x))
                    throw;
                return default(T);
            }
        }

        /// <summary>
        /// 执行命令并返回第一行第一列数据
        /// </summary>
        /// <typeparam name="T">目标数据类型</typeparam>
        /// <param name="storeProc">存储过程</param>
        /// <returns>返回第一行第一列数据</returns>
        public T ExecuteScalar<T>(StoreProc storeProc)
        {
            try
            {
                OpenSharedConnection();
                try
                {
                    using (var cmd = CreateCommand(_sharedConnection, storeProc))
                    {
                        _provider.BeforeExecute(cmd);
                        DoPreExecute(cmd);
                        OnExecutingCommand(cmd);
                        object val = cmd.ExecuteScalar();
                        OnExecutedCommand(cmd);

                        // Handle nullable types
                        Type u = Nullable.GetUnderlyingType(typeof(T));
                        if (u != null && val == null)
                            return default(T);

                        return (T)Convert.ChangeType(val, u ?? typeof(T));
                    }
                }
                finally
                {
                    CloseSharedConnection();
                }
            }
            catch (Exception x)
            {
                if (OnException(x))
                    throw;
                return default(T);
            }
        }

        /// <summary>
        /// 执行命令并返回第一行第一列数据
        /// </summary>
        /// <typeparam name="T">目标数据类型</typeparam>
        /// <param name="cmd">命令对象</param>
        /// <returns>返回第一行第一列数据</returns>
        public T ExecuteScalar<T>(DbCommand cmd)
        {
            try
            {
                OpenSharedConnection();
                try
                {
                    cmd.Connection = _sharedConnection;
                    cmd.Transaction = _transaction;
                    _provider.BeforeExecute(cmd);
                    DoPreExecute(cmd);
                    OnExecutingCommand(cmd);
                    object val = cmd.ExecuteScalar();
                    OnExecutedCommand(cmd);

                    Type u = Nullable.GetUnderlyingType(typeof(T));
                    if (u != null && val == null)
                        return default(T);

                    return (T)Convert.ChangeType(val, u ?? typeof(T));
                }
                finally
                {
                    CloseSharedConnection();
                }
            }
            catch (Exception x)
            {
                if (OnException(x))
                    throw;
                return default(T);
            }
        }

        #endregion

        #region ExecuteDataSet

        /// <summary>
        /// 执行命令并返回数据集(参数值数组)
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="arrayArgs">参数值数组</param>
        /// <returns>返回新创建的数据集</returns>
        public DataSet ExecuteDataSet(string sql, object[] arrayArgs)
        {
            return ExecuteDataSet(sql, args: arrayArgs);
        }

        /// <summary>
        /// 执行命令并返回数据集(参数字典)
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="dicArgs">参数字典</param>
        /// <returns>返回新创建的数据集</returns>
        public DataSet ExecuteDataSet(string sql, IDictionary<string, object> dicArgs)
        {
            return ExecuteDataSet(sql, args: dicArgs);
        }

        /// <summary>
        /// 执行命令并返回数据集(参数对象)
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="args">参数对象</param>
        /// <returns>返回新创建的数据集</returns>
        public DataSet ExecuteDataSet(string sql, object args = null)
        {
            try
            {
                OpenSharedConnection();
                try
                {
                    DataSet ds = new DataSet();
                    using (var cmd = CreateCommand(_sharedConnection, sql, args))
                    {
                        using (DbDataAdapter dbDataAdapter = CreateDataAdapter())
                        {
                            // ReSharper disable once PossibleNullReferenceException
                            dbDataAdapter.SelectCommand = cmd;
                            _provider.BeforeExecute(cmd);
                            DoPreExecute(cmd);
                            OnExecutingCommand(cmd);
                            dbDataAdapter.Fill(ds);
                            OnExecutedCommand(cmd);
                        }
                    }
                    return ds;
                }
                finally
                {
                    CloseSharedConnection();
                }
            }
            catch (Exception x)
            {
                if (OnException(x))
                    throw;
                return null;
            }
        }

        /// <summary>
        /// 执行命令并返回数据集
        /// </summary>
        /// <param name="storeProc">存储过程</param>
        /// <returns>返回新创建的数据集</returns>
        public DataSet ExecuteDataSet(StoreProc storeProc)
        {
            try
            {
                OpenSharedConnection();
                try
                {
                    DataSet ds = new DataSet();
                    using (var cmd = CreateCommand(_sharedConnection, storeProc))
                    {
                        using (DbDataAdapter dbDataAdapter = CreateDataAdapter())
                        {
                            dbDataAdapter.SelectCommand = cmd;
                            _provider.BeforeExecute(cmd);
                            DoPreExecute(cmd);
                            OnExecutingCommand(cmd);
                            dbDataAdapter.Fill(ds);
                            OnExecutedCommand(cmd);
                        }
                    }
                    return ds;
                }
                finally
                {
                    CloseSharedConnection();
                }
            }
            catch (Exception x)
            {
                if (OnException(x))
                    throw;
                return null;
            }
        }

        /// <summary>
        /// 执行命令并返回数据集
        /// </summary>
        /// <param name="cmd">命令对象</param>
        /// <returns>返回新创建的数据集</returns>
        public DataSet ExecuteDataSet(DbCommand cmd)
        {
            try
            {
                OpenSharedConnection();
                try
                {
                    DataSet ds = new DataSet();
                    using (DbDataAdapter dbDataAdapter = CreateDataAdapter())
                    {
                        cmd.Connection = _sharedConnection;
                        cmd.Transaction = _transaction;
                        dbDataAdapter.SelectCommand = cmd;
                        _provider.BeforeExecute(cmd);
                        DoPreExecute(cmd);
                        OnExecutingCommand(cmd);
                        dbDataAdapter.Fill(ds);
                        OnExecutedCommand(cmd);
                    }
                    return ds;
                }
                finally
                {
                    CloseSharedConnection();
                }
            }
            catch (Exception x)
            {
                if (OnException(x))
                    throw;
                return null;
            }
        }

        #endregion

        #region Query

        ///// <summary>
        ///// 将Reader集合转换成字符串
        ///// </summary>
        //private string ConvertReadersToJson(IDataReader reader)
        //{
        //    var jsonStr = new StringBuilder();
        //    jsonStr.Append("[");

        //    while (reader.Read())
        //    {
        //        try
        //        {
        //            ConvertSingleReaderToJson(reader, jsonStr);
        //            jsonStr.Append(",");
        //        }
        //        catch (Exception x)
        //        {
        //            if (OnException(x))
        //                throw;
        //        }
        //    }

        //    if (jsonStr.Length > 3)
        //    {
        //        jsonStr.Length = jsonStr.Length - 1;
        //        jsonStr.Append("]");
        //        return jsonStr.ToString();
        //    }

        //    return string.Empty;
        //}

        ///// <summary>
        ///// 将一个Reader转换成Json字符串，reader必须可读.read
        ///// </summary>
        //public void ConvertSingleReaderToJson(IDataReader reader, StringBuilder jsonStr)
        //{
        //    jsonStr.Append("{");

        //    for (var i = 0; i < reader.FieldCount; i++)
        //    {
        //        //值类型数据不需要引号，否则需要
        //        var fieldType = reader.GetFieldType(i);
        //        object fileValue = reader[i];

        //        if (fieldType == typeof(DateTime))
        //        {
        //            DateTime dt = DateTime.MinValue;

        //            //if (DateTime.TryParse(fileValue.ToString(), out dt))
        //            //{
        //            //    fileValue = ConvertDateTimeToJson(dt);
        //            //}
        //        }
        //        //日期作为特殊情况已经处理过了
        //        var comma = (fieldType == typeof(string) || fieldType == typeof(Guid)) ? "\"" : "";
        //        jsonStr.AppendFormat("\"{0}\":{2}{1}{2},", reader.GetName(i), fileValue, comma);
        //    }
        //    //去掉多余的逗号
        //    jsonStr.Length = jsonStr.Length - 1;
        //    jsonStr.Append("}");
        //}

        ///// <summary>
        ///// 查询数据,返回Json字符串
        ///// </summary>
        ///// <typeparam name="T">实体类型</typeparam>
        ///// <param name="sql">查询语句</param>
        ///// <param name="args">参数对象</param>
        ///// <returns>返回记录集合</returns>
        //public string QueryJson(string sql, object args = null)
        //{
        //    OpenSharedConnection();
        //    try
        //    {
        //        using (var cmd = CreateCommand(_sharedConnection, sql, args))
        //        {
        //            IDataReader r = null;
        //            try
        //            {
        //                _provider.BeforeExecute(cmd);
        //                DoPreExecute(cmd);
        //                OnExecutingCommand(cmd);
        //                r = cmd.ExecuteReader();
        //                OnExecutedCommand(cmd);
        //            }
        //            catch (Exception x)
        //            {
        //                if (OnException(x))
        //                    throw;
        //            }
        //            using (r)
        //            {
        //                return ConvertReadersToJson(r);
        //            }
        //        }
        //    }
        //    finally
        //    {
        //        CloseSharedConnection();
        //    }
        //}

        /// <summary>
        /// 查询数据(参数对象)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">查询语句</param>
        /// <param name="args">参数对象</param>
        /// <returns>返回记录集合</returns>
        public IEnumerable<T> Query<T>(string sql, object args = null)
        {
            OpenSharedConnection();
            try
            {
                using (var cmd = CreateCommand(_sharedConnection, sql, args))
                {
                    IDataReader r;
                    var pd = PocoData.ForType(typeof(T));
                    try
                    {
                        _provider.BeforeExecute(cmd);
                        DoPreExecute(cmd);
                        OnExecutingCommand(cmd);
                        r = cmd.ExecuteReader();
                        OnExecutedCommand(cmd);
                    }
                    catch (Exception x)
                    {
                        if (OnException(x))
                            throw;
                        yield break;
                    }
                    var factory = pd.GetFactory(cmd.CommandText, _sharedConnection.ConnectionString, 0, r.FieldCount, r) as Func<IDataReader, T>;
                    using (r)
                    {
                        while (true)
                        {
                            T poco;
                            try
                            {
                                if (!r.Read())
                                    yield break;
                                // ReSharper disable once PossibleNullReferenceException
                                poco = factory(r);
                            }
                            catch (Exception x)
                            {
                                if (OnException(x))
                                    throw;
                                yield break;
                            }

                            yield return poco;
                        }
                    }
                }
            }
            finally
            {
                CloseSharedConnection();
            }
        }

        /// <summary>
        /// 查询数据(参数对象)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="cmd">命令对象</param>
        /// <returns>返回记录集合</returns>
        public IEnumerable<T> Query<T>(DbCommand cmd)
        {
            OpenSharedConnection();
            try
            {
                IDataReader r;
                var pd = PocoData.ForType(typeof(T));
                try
                {
                    _provider.BeforeExecute(cmd);
                    DoPreExecute(cmd);
                    OnExecutingCommand(cmd);
                    r = cmd.ExecuteReader();
                    OnExecutedCommand(cmd);
                }
                catch (Exception x)
                {
                    if (OnException(x))
                        throw;
                    yield break;
                }
                var factory = pd.GetFactory(cmd.CommandText, _sharedConnection.ConnectionString, 0, r.FieldCount, r) as Func<IDataReader, T>;
                using (r)
                {
                    while (true)
                    {
                        T poco;
                        try
                        {
                            if (!r.Read())
                                yield break;
                            // ReSharper disable once PossibleNullReferenceException
                            poco = factory(r);
                        }
                        catch (Exception x)
                        {
                            if (OnException(x))
                                throw;
                            yield break;
                        }

                        yield return poco;
                    }
                }
            }
            finally
            {
                CloseSharedConnection();
            }
        }

        /// <summary>
        /// 查询数据(参数对象)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="storeProc">存储过程</param>
        /// <returns>返回记录集合</returns>
        public IEnumerable<T> Query<T>(StoreProc storeProc)
        {
            OpenSharedConnection();
            try
            {
                using (var cmd = CreateCommand(_sharedConnection, storeProc))
                {
                    IDataReader r;
                    var pd = PocoData.ForType(typeof(T));
                    try
                    {
                        _provider.BeforeExecute(cmd);
                        DoPreExecute(cmd);
                        OnExecutingCommand(cmd);
                        r = cmd.ExecuteReader();
                        OnExecutedCommand(cmd);
                    }
                    catch (Exception x)
                    {
                        if (OnException(x))
                            throw;
                        yield break;
                    }
                    var factory = pd.GetFactory(cmd.CommandText, _sharedConnection.ConnectionString, 0, r.FieldCount, r) as Func<IDataReader, T>;
                    using (r)
                    {
                        while (true)
                        {
                            T poco;
                            try
                            {
                                if (!r.Read())
                                    yield break;
                                // ReSharper disable once PossibleNullReferenceException
                                poco = factory(r);
                            }
                            catch (Exception x)
                            {
                                if (OnException(x))
                                    throw;
                                yield break;
                            }

                            yield return poco;
                        }
                    }
                }
            }
            finally
            {
                CloseSharedConnection();
            }
        }

        #endregion

        #region Repository

        /// <summary>
        /// 是否存在指定条件的记录(查询表达式)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="expression">查询表达式</param>
        /// <returns>存在返回true,否则返回false</returns>
        public bool Exists<T>(Expression<Func<T, bool>> expression)
        {
            var meta = EntityHelper.Get<T>();
            var result = SqlExpressionCompiler.Compile(0, new[] { expression });
            var sql = $"select 1 from {meta.TableAttr.DbName} where {result.SQL}";
            var results = ExecuteScalar<int?>(sql, result.Parameters);
            return results.HasValue && results.Value == 1;
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entity">实体对象(默认写入值不为null的属性)</param>
        /// <returns>返回受影响的行数</returns>
        public int Insert<T>(T entity)
        {
            var meta = EntityHelper.Get<T>();
            var names = new List<string>();
            var values = new List<string>();
            var paramDic = new Dictionary<string, object>();
            foreach (var i in meta.DbColumns)
            {
                var value = i.Value.GetValue(entity);
                if (meta.PrimaryAttr != null && meta.PrimaryProperty.Name.Equals(i.Key)) //主键
                {
                    if (meta.PrimaryAttr.Auto)//自增主键
                    {
                        continue;
                    }
                    else if (value == null) //guid主键
                    {
                        value = StringHelper.Guid();
                    }
                }

                if (value == null)
                {
                    continue;
                }

                names.Add(i.Value.DbName);
                values.Add($"@{i.Value.DbName}");
                paramDic.Add(i.Value.DbName, value);
            }

            var sql =
                $"insert into {meta.TableAttr.DbName} ({string.Join(",", names.ToArray())}) values ({string.Join(",", values.ToArray())})";
            var cmd = CreateCommand(sql, paramDic);
            _provider.PrepInsert(meta, cmd);
            if (meta.PrimaryAttr != null && meta.PrimaryAttr.Auto)
            {
                var id = ExecuteScalar<object>(cmd);
                meta.PrimaryProperty.SetValue(entity, id);
                return 1;
            }
            return Execute(cmd);
        }

        /// <summary>
        /// 插入数据(指定写入的列)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entity">实体对象</param>
        /// <param name="cols">写入的列数组(null为所有列)</param>
        /// <returns>返回受影响的行数</returns>
        public int InsertInclude<T>(T entity, string[] cols = null)
        {
            var meta = EntityHelper.Get<T>();
            var names = new List<string>();
            var values = new List<string>();
            var paramDic = new Dictionary<string, object>();
            Dictionary<string, EntityColumn> insertCols = null;
            if (cols == null || cols.Length == 0)
            {
                insertCols = meta.DbColumns;
            }
            else
            {
                insertCols = new Dictionary<string, EntityColumn>();
                foreach (var col in cols)
                {
                    if (meta.DbColumns.TryGetValue(col, out EntityColumn entityCol))
                    {
                        insertCols.Add(col, entityCol);
                    }
                }
            }

            foreach (var i in insertCols)
            {
                var value = i.Value.GetValue(entity);
                if (meta.PrimaryAttr != null && meta.PrimaryProperty.Name.Equals(i.Key)) //主键
                {
                    if (meta.PrimaryAttr.Auto)//自增主键
                    {
                        continue;
                    }
                    else if (value == null) //guid主键
                    {
                        value = StringHelper.Guid();
                    }
                }

                names.Add(i.Value.DbName);
                values.Add($"@{i.Value.DbName}");
                paramDic.Add(i.Value.DbName, value);
            }

            var sql =
                $"insert into {meta.TableAttr.DbName} ({string.Join(",", names.ToArray())}) values ({string.Join(",", values.ToArray())})";
            var cmd = CreateCommand(sql, paramDic);
            _provider.PrepInsert(meta, cmd);
            if (meta.PrimaryAttr != null && meta.PrimaryAttr.Auto)
            {
                var id = ExecuteScalar<object>(cmd);
                meta.PrimaryProperty.SetValue(entity, id);
                return 1;
            }
            return Execute(cmd);
        }

        /// <summary>
        /// 插入数据(指定排除的列)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entity">实体对象</param>
        /// <param name="excludeCols">排除写入的列数组(不允许为空)</param>
        /// <returns>返回受影响的行数</returns>
        public int InsertExclude<T>(T entity, string[] excludeCols)
        {
            if (excludeCols == null || excludeCols.Length == 0)
            {
                throw new ArgumentNullException(nameof(excludeCols), "请指定排除列数组");
            }
            var meta = EntityHelper.Get<T>();
            var cols = meta.DbColumns.Select(p => p.Key);
            return InsertInclude<T>(entity, cols.Except(excludeCols).ToArray());
        }

        /// <summary>
        /// 插入数据(需要写入的列和值对象,支持对象类型,不检查主键)
        /// </summary>
        /// <param name="obj">写入对象(需要写入的列和值对象,支持对象类型,不检查主键字段)</param>
        /// <param name="tableName">表名称</param>
        /// <returns>返回受影响的行数</returns>
        public int InsertObject(object obj, string tableName)
        {
            var names = new List<string>();
            var values = new List<string>();
            var paramDic = new Dictionary<string, object>();
            var objType = obj.GetType();
            foreach (PropertyDescriptor propertyDescriptor in TypeDescriptor.GetProperties(obj))
            {
                string name = propertyDescriptor.Name;
                names.Add(name);
                values.Add($"@{name}");
                paramDic.Add(name, objType.GetProperty(name).GetValue(obj, null));
            }

            var sql =
                $"INSERT INTO {tableName} ({string.Join(",", names.ToArray())}) VALUES ({string.Join(",", values.ToArray())})";
            return Execute(sql, paramDic);
        }

        /// <summary>
        /// 更新实体数据(查询表达式)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entity">实体对象(默认写入值不为null的属性)</param>
        /// <returns>返回受影响的行数</returns>
        public int Update<T>(T entity)
        {
            var meta = EntityHelper.Get<T>();
            if (meta.PrimaryProperty == null)
            {
                throw new ArgumentException($"请指定实体({meta.TableAttr.Name})主键字段");
            }
            var exp = ExpressionHelper.BuildExpression<T>(meta.PrimaryProperty.Name, meta.PrimaryProperty.GetValue(entity));
            return Update(entity, exp);
        }

        /// <summary>
        /// 更新实体数据(查询表达式)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entity">实体对象(默认写入值不为null的属性)</param>
        /// <param name="expression">查询表达式</param>
        /// <returns>返回受影响的行数</returns>
        public int Update<T>(T entity, Expression<Func<T, bool>> expression)
        {
            var meta = EntityHelper.Get<T>();
            var paramDic = new Dictionary<string, object>();
            StringBuilder values = new StringBuilder();

            foreach (var i in meta.DbColumns)
            {
                if (meta.PrimaryProperty != null && meta.PrimaryProperty.Name.Equals(i.Key)) //主键不更新
                {
                    continue;
                }

                var value = i.Value.GetValue(entity);
                if (value == null)
                {
                    continue;
                }

                values.AppendFormat("{0}=@{0},", i.Value.DbName);
                paramDic.Add(i.Key, value);
            }
            var result = SqlExpressionCompiler.Compile(0, new[] { expression });
            foreach (var pair in result.Parameters)
            {
                paramDic.Add(pair.Key, pair.Value);
            }
            string sql = $"update {meta.TableAttr.DbName} set {values.ToString().TrimEnd(',')} where {result.SQL}";
            return Execute(sql, paramDic);
        }

        /// <summary>
        /// 更新实体数据((指定更新的列)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entity">实体对象(默认写入值不为null的属性)</param>
        /// <param name="expression">查询表达式</param>
        /// <param name="cols">更新的列数组(null为所有列)</param>
        /// <returns>返回受影响的行数</returns>
        public int UpdateInclude<T>(T entity, Expression<Func<T, bool>> expression, string[] cols = null)
        {
            var meta = EntityHelper.Get<T>();
            var paramDic = new Dictionary<string, object>();
            StringBuilder values = new StringBuilder();
            Dictionary<string, EntityColumn> updateCols = null;
            if (cols == null || cols.Length == 0)
            {
                updateCols = meta.DbColumns;
            }
            else
            {
                updateCols = new Dictionary<string, EntityColumn>();
                foreach (var col in cols)
                {
                    if (meta.DbColumns.TryGetValue(col, out EntityColumn entityCol))
                    {
                        updateCols.Add(col, entityCol);
                    }
                }
            }

            foreach (var i in updateCols)
            {
                if (meta.PrimaryProperty != null && meta.PrimaryProperty.Name.Equals(i.Key)) //主键不更新
                {
                    continue;
                }

                var value = i.Value.GetValue(entity);

                values.AppendFormat("{0}=@{0},", i.Value.DbName);
                paramDic.Add(i.Key, value);
            }
            var result = SqlExpressionCompiler.Compile(0, new[] { expression });
            foreach (var pair in result.Parameters)
            {
                paramDic.Add(pair.Key, pair.Value);
            }
            string sql = $"update {meta.TableAttr.DbName} set {values.ToString().TrimEnd(',')} where {result.SQL}";
            return Execute(sql, paramDic);
        }

        /// <summary>
        /// 更新实体数据(指定排除的列)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entity">实体对象(默认写入值不为null的属性)</param>
        /// <param name="expression">查询表达式</param>
        /// <param name="excludeCols">排除更新的列数组(不允许为空)</param>
        /// <returns>返回受影响的行数</returns>
        public int UpdateExclude<T>(T entity, Expression<Func<T, bool>> expression, string[] excludeCols)
        {
            if (excludeCols == null || excludeCols.Length == 0)
            {
                throw new ArgumentNullException(nameof(excludeCols), "请指定排除更新列数组");
            }
            var meta = EntityHelper.Get<T>();
            var cols = meta.DbColumns.Select(p => p.Key);
            return UpdateInclude<T>(entity, expression, cols.Except(excludeCols).ToArray());
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="id">主键</param>
        /// <returns>返回受影响的行数</returns>
        public int Delete<T>(string id)
        {
            var meta = EntityHelper.Get<T>();
            if (meta.PrimaryProperty == null)
            {
                throw new ArgumentException($"请指定实体({meta.TableAttr.Name})主键字段");
            }
            var exp = ExpressionHelper.BuildExpression<T>(meta.PrimaryProperty.Name, id);
            return Delete(exp);
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entity">实体对象</param>
        /// <returns>返回受影响的行数</returns>
        public int Delete<T>(T entity)
        {
            var meta = EntityHelper.Get<T>();
            if (meta.PrimaryProperty == null)
            {
                throw new ArgumentException($"请指定实体({meta.TableAttr.Name})主键字段");
            }
            var exp = ExpressionHelper.BuildExpression<T>(meta.PrimaryProperty.Name, meta.PrimaryProperty.GetValue(entity));
            return Delete(exp);
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="expression">查询表达式</param>
        /// <returns>返回受影响的行数</returns>
        public int Delete<T>(Expression<Func<T, bool>> expression)
        {
            var meta = EntityHelper.Get<T>();
            var result = SqlExpressionCompiler.Compile(0, new[] { expression });
            var sql = $"delete from {meta.TableAttr.DbName} where {result.SQL}";
            return Execute(sql, result.Parameters);
        }

        /// <summary>
        /// 获取实体对象
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="id">主键</param>
        /// <returns>返回实体对象</returns>
        public T Get<T>(string id)
        {
            var meta = EntityHelper.Get<T>();
            if (meta.PrimaryProperty == null)
            {
                throw new ArgumentException($"请指定实体({meta.TableAttr.Name})主键字段");
            }
            var exp = ExpressionHelper.BuildExpression<T>(meta.PrimaryProperty.Name, id);
            return Get(exp);
        }

        /// <summary>
        /// 获取实体对象
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="expression">查询表达式</param>
        /// <returns>返回实体对象</returns>
        public T Get<T>(Expression<Func<T, bool>> expression)
        {
            var linq = SQLQuery<T>.Instance.Where(expression);
            return Get(linq);
        }

        /// <summary>
        /// 获取实体对象
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sqlQuery">查询表达式</param>
        /// <returns>返回实体对象</returns>
        public T Get<T>(SQLQuery<T> sqlQuery)
        {
            return Query<T>(sqlQuery).FirstOrDefault();
        }

        /// <summary>
        /// 获取单个字段值
        /// </summary>
        /// <typeparam name="V">值类型</typeparam>
        /// <typeparam name="T">实体类型</typeparam>
        /// <returns>返回单个字段值</returns>
        public V GetValue<V, T>(SQLQuery<T> sqlQuery)
        {
            var result = sqlQuery.ToResult();
            return ExecuteScalar<V>(result.ToSQL(), result.Parameters);
        }

        /// <summary>
        /// 获取单个字段值
        /// </summary>
        /// <typeparam name="V">值类型</typeparam>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="expression">查询表达式</param>
        /// <param name="colName">指定列</param>
        /// <returns>返回单个字段值</returns>
        public V GetValue<V, T>(Expression<Func<T, bool>> expression, Expression<Func<T, object>> colName)
        {
            var col = ExpressionHelper.GetPropertyName(colName);
            var result = SQLQuery<T>.Instance.Where(expression).Select(col).ToResult();
            return ExecuteScalar<V>(result.ToSQL(), result.Parameters);
        }

        /// <summary>
        /// 获取合计列
        /// </summary>
        /// <param name="colName">指定列</param>
        /// <returns>返回取合计列</returns>
        public int? Sum<T>(Expression<Func<T, object>> colName)
        {
            var result = SQLQuery<T>.Instance.Sum(colName).ToResult();
            return ExecuteScalar<int?>(result.ToSQL(), result.Parameters);
        }

        /// <summary>
        /// 获取合计列
        /// </summary>
        /// <param name="colName">指定列</param>
        /// <param name="expression">查询表达式</param>
        /// <returns>返回取合计列</returns>
        public int? Sum<T>(Expression<Func<T, object>> colName, Expression<Func<T, bool>> expression)
        {
            var result = SQLQuery<T>.Instance.Where(expression).Sum(colName).ToResult();
            return ExecuteScalar<int?>(result.ToSQL(), result.Parameters);
        }

        /// <summary>
        /// 获取记录数
        /// </summary>
        /// <returns>返回记录数</returns>
        public int Count<T>()
        {
            var result = SQLQuery<T>.Instance.Count().ToResult();
            return ExecuteScalar<int>(result.ToSQL(), result.Parameters);
        }

        /// <summary>
        /// 获取记录数
        /// </summary>
        /// <param name="expression">查询表达式</param>
        /// <returns>返回记录数</returns>
        public int Count<T>(Expression<Func<T, bool>> expression)
        {
            var result = SQLQuery<T>.Instance.Where(expression).Count().ToResult();
            return ExecuteScalar<int>(result.ToSQL(), result.Parameters);
        }

        /// <summary>
        /// 查询数据查询数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <returns>返回记录集合</returns>
        public IEnumerable<T> Query<T>()
        {
            return Query<T>(new SQLQuery<T>());
        }

        /// <summary>
        /// 查询数据查询数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <returns>返回记录集合</returns>
        public IEnumerable<T> Query<T>(Expression<Func<T, bool>> expression)
        {
            var linq = SQLQuery<T>.Instance.Where(expression);
            return Query(linq);
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sqlQuery">查询表达式</param>
        /// <returns>返回记录集合</returns>
        public IEnumerable<T> Query<T>(SQLQuery<T> sqlQuery)
        {
            var result = sqlQuery.ToResult();
            return Query<T>(result.ToSQL(), result.Parameters);
        }

        /// <summary>
        /// 查询DataSet
        /// </summary>
        /// <param name="sqlQuery">查询表达式</param>
        /// <returns>返回DataTable</returns>
        public DataSet GetSet<T>(SQLQuery<T> sqlQuery)
        {
            var result = sqlQuery.ToResult();
            return ExecuteDataSet(result.ToSQL(), result.Parameters);
        }

        /// <summary>
        /// 查询DataTable
        /// </summary>
        /// <param name="sqlQuery">查询表达式</param>
        /// <returns>返回DataTable</returns>
        public DataTable GetTable<T>(SQLQuery<T> sqlQuery)
        {
            var result = sqlQuery.ToResult();
            var ds = ExecuteDataSet(result.ToSQL(), result.Parameters);
            return ds.Tables.Count > 0 ? ds.Tables[0] : null;
        }

        /// <summary>
        /// 查询分页数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sqlQuery">查询表达式</param>
        /// <returns>返回分页列表</returns>
        public PagedList<T> Page<T>(SQLQuery<T> sqlQuery)
        {
            if (sqlQuery == null) throw new ArgumentNullException(nameof(sqlQuery), "查询表达式不允许为空");

            var result = (SQLinqSelectResult)sqlQuery.ToResult();
            if (result.Skip == null) throw new ArgumentNullException(nameof(sqlQuery), "请指定Skip属性");
            if (result.Take == null) throw new ArgumentNullException(nameof(sqlQuery), "请指定Take属性");

            int startIndex = result.Skip.Value + 1;
            int endIndex = result.Skip.Value + (result.Take.Value);
            var dataSql = new StringBuilder();
            var countSql = new StringBuilder();

            var orderby = SQLinqSelectResult.ConcatFieldArray(result.OrderBy);
            var groupby = SQLinqSelectResult.ConcatFieldArray(result.GroupBy);
            var columnsString = SQLinqSelectResult.ConcatFieldArray(result.Select);

            if (string.IsNullOrEmpty(result.SubQuery))
            {
                dataSql.AppendFormat("select {0} from {1}", columnsString, sqlQuery.GetTableName());
                countSql.AppendFormat("select count(1) from {0}", sqlQuery.GetTableName());
            }
            else
            {
                dataSql.AppendFormat("select * from ( {0} ) subgrid", result.SubQuery);
                countSql.AppendFormat("select count(1) from ( {0} ) subgrid", result.SubQuery);
            }

            if (!string.IsNullOrEmpty(result.Where))
            {
                dataSql.AppendFormat(" where {0}", result.Where);
                countSql.AppendFormat(" where {0}", result.Where);
            }

            if (!string.IsNullOrEmpty(groupby))
            {
                dataSql.AppendFormat(" group by {0}", groupby);
                countSql.AppendFormat(" group by {0}", groupby);
            }

            if (!string.IsNullOrEmpty(result.Having))
            {
                dataSql.AppendFormat(" having {0}", result.Having);
                countSql.AppendFormat(" having {0}", result.Having);
            }

            var orderByString = string.Empty;
            if (orderby.Length > 0)
            {
                orderByString = " order by " + orderby;
            }

            string sql = _provider.GetPageStatement(dataSql.ToString(), orderByString, startIndex, endIndex);
            int totalCount = ExecuteScalar<int>(countSql.ToString(), result.Parameters);
            var data = Query<T>(sql, result.Parameters).ToList();
            return new PagedList<T>(result.PageIndex, result.Take.Value, totalCount, data);
        }

        public PagedList<T> Page<T>(int pageIndex, int pageSize, string sql, object args = null)
        {
            PagingHelper.SplitSQL(sql, out var part);
            var skip = (pageIndex - 1) * pageSize;
            sql = _provider.BuildPageQuery(skip, pageSize, part);
            int totalCount = ExecuteScalar<int>(part.sqlCount, args);
            var data = Query<T>(sql, args).ToList();
            return new PagedList<T>(pageIndex, pageSize, totalCount, data);
        }

        #endregion

        #region 日志管理

        /// <summary>
        /// 当打开数据库连接时调用
        /// </summary>
        /// <param name="conn">数据库连接对象</param>
        protected virtual void OnConnectionOpened(DbConnection conn)
        {
            logger.Debug("打开连接", R.LogTag.Database);
#if DEBUG
            Debug.WriteLine(string.Format("{0} 打开连接", DateTimeHelper.FormatDateHasMilliSecond(DateTime.Now)), "Database");
#endif

        }

        /// <summary>
        /// 当关闭数据库连接时调用
        /// </summary>
        /// <param name="conn">数据库连接对象</param>
        protected virtual void OnConnectionClosing(DbConnection conn)
        {
            logger.Debug("关闭连接", R.LogTag.Database);
#if DEBUG
            Debug.WriteLine(string.Format("{0} 关闭连接", DateTimeHelper.FormatDateHasMilliSecond(DateTime.Now)), "Database");
#endif
        }

        /// <summary>
        /// 当事务开始执行时调用
        /// </summary>
        protected virtual void OnBeginTransaction()
        {
            logger.Debug("开启事务", R.LogTag.Database);
#if DEBUG
            Debug.WriteLine(string.Format("{0} 开启事务", DateTimeHelper.FormatDateHasMilliSecond(DateTime.Now)), "Database");
#endif
        }

        /// <summary>
        /// 当事务结束时调用
        /// </summary>
        protected virtual void OnEndTransaction()
        {
            if (_transactionCancelled)
            {
                logger.Debug("回滚事务", R.LogTag.Database);
#if DEBUG
                Debug.WriteLine(string.Format("{0} 回滚事务", DateTimeHelper.FormatDateHasMilliSecond(DateTime.Now)), "Database");
#endif
            }
            else
            {
                logger.Debug("提交事务", R.LogTag.Database);
#if DEBUG
                Debug.WriteLine(string.Format("{0} 提交事务", DateTimeHelper.FormatDateHasMilliSecond(DateTime.Now)), "Database");
#endif
            }
        }

        /// <summary>
        /// 当执行数据库查询发送异常时执行
        /// </summary>
        /// <param name="x">异常对象</param>
        /// <returns>返回true则重新抛出异常,否则返回false.</returns>
        protected virtual bool OnException(Exception x)
        {
            logger.Debug($"执行异常->{x.Message}", R.LogTag.Database);
#if DEBUG
            Debug.WriteLine(string.Format("执行异常 {0} {1}",
                DateTimeHelper.FormatDateHasMilliSecond(DateTime.Now), x.Message), "Database");
#endif
            return true;
        }


        /// <summary>
        /// 当命令对象执行前调用
        /// </summary>
        /// <param name="cmd">命令对象</param>
        protected virtual void OnExecutingCommand(DbCommand cmd)
        {
            _watch.Start();
        }

        /// <summary>
        /// 当命令对象执行完成时调用
        /// </summary>
        /// <param name="cmd">命令对象</param>
        protected virtual void OnExecutedCommand(DbCommand cmd)
        {
            _watch.Stop();
            var msg = $"执行命令:{FormatCommand(cmd)} 执行耗时:{_watch.Elapsed.TotalMilliseconds:###0.##}毫秒";
            logger.Debug(msg, R.LogTag.Database);
#if DEBUG
            Debug.WriteLine($"{DateTimeHelper.FormatDateHasMilliSecond(DateTime.Now)} {msg}", "Database");
#endif
            _watch.Reset();
        }

        /// <summary>
        /// 格式化显示命令对象
        /// </summary>
        /// <param name="cmd">命令对象</param>
        /// <returns>返回格式化显示命令对象字符串</returns>
        private string FormatCommand(DbCommand cmd)
        {
            var sb = new StringBuilder();
            var args = (from IDbDataParameter parameter in cmd.Parameters select parameter).ToArray();
            if (args.Length > 0)
            {
                sb.Append("\n");
                for (int i = 0; i < args.Length; i++)
                {
                    var param = args[i];
                    sb.AppendFormat("\tdeclare {0}{1} {2} = '{3}'\n", _paramPrefix,
                        param.ParameterName, param.DbType, param.Value);
                }
                sb.Remove(sb.Length - 1, 1);
                sb.Append("\n");
            }
            string sql = cmd.CommandText;
            if (string.IsNullOrEmpty(sql))
                return string.Empty;
            sb.Append("\t");
            sb.Append(sql);
            return sb.ToString();
        }

        #endregion

        #region 私有方法

        /// <summary>
        /// 设置命令对象属性
        /// </summary>
        /// <param name="cmd">命令对象</param>
        private void DoPreExecute(DbCommand cmd)
        {
            if (OneTimeCommandTimeout != 0)
            {
                cmd.CommandTimeout = OneTimeCommandTimeout;
                OneTimeCommandTimeout = 0;
            }
            else if (CommandTimeout != 0)
            {
                cmd.CommandTimeout = CommandTimeout;
            }
        }

        /// <summary>
        /// 数据类型转换
        /// </summary>
        /// <param name="value">参数值</param>
        /// <returns>返回数据库对应的数据类型</returns>
        public virtual object ConvertParamValue(object value)
        {
            //把布尔值转换为整形
            if (value is bool b)
            {
                return b ? 1 : 0;
            }

            return value;
        }

        #endregion

        #region 测试连接

        /// <summary>
        /// 测试数据库连接
        /// </summary>
        /// <param name="timeout">超时时间</param>
        /// <returns>连接成功 返回true</returns>
        public BoolMessage TestConnection(int timeout = 3000)
        {
            Stopwatch sw = new Stopwatch();
            BoolMessage connectSuccess = new BoolMessage(false);

            DbConnection conn = null;

            void TestAction()
            {
                try
                {
                    conn = CreateConnection();
                    // ReSharper disable once PossibleNullReferenceException
                    conn.ConnectionString = _setting.ConnectionString;
                    sw.Start();
                    conn.Open();
                    connectSuccess = new BoolMessage(true);
                }
                catch (Exception e)
                {
                    connectSuccess = new BoolMessage(false, e.Message);
                }
                finally
                {
                    if (conn != null && conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
            }

            Thread t = new Thread(TestAction);
            t.IsBackground = true;
            t.Start();

            // ReSharper disable once LoopVariableIsNeverChangedInsideLoop
            while (timeout > sw.ElapsedMilliseconds)
            {
                if (t.Join(1000))
                {
                    break;
                }
            }

            return connectSuccess;
        }

        #endregion

        #region IDisposable

        /// <summary>
        /// 执行与释放或重置非托管资源相关的应用程序定义的任务。
        /// </summary>
        public void Dispose()
        {
            CloseSharedConnection();
        }

        #endregion
    }
}